﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Collections;

/// <summary>
/// Summary description for NguoiDungDAO
/// </summary>
public class NguoiDungDAO
{
    ConnectionDB ConDB;
    public NguoiDungDAO()
    {
        ConDB = new ConnectionDB();
    }

    public DataTable GetNguoiDungSearch(NguoiDung nguoiDung, DateTime? dateEnd)
    {
        Hashtable hashtable = new Hashtable();

        StringBuilder sql = new StringBuilder();
        sql.Append("SELECT * FROM T_NGUOI_DUNG WHERE 1=1");

        if (nguoiDung == null)
            goto NguoiDungNull;

        if (!string.IsNullOrEmpty(nguoiDung.Email))
        {
            sql.Append(" AND Email LIKE @Email");
            hashtable["@Email"] = "%" + nguoiDung.Email + "%";
        }

        if (!string.IsNullOrEmpty(nguoiDung.Username))
        {
            sql.Append(" AND UserName LIKE @UserName");
            hashtable["@UserName"] = "%" + nguoiDung.Username + "%";
        }

        if (!string.IsNullOrEmpty(nguoiDung.Fullname))
        {
            sql.Append(" AND HoTen LIKE @HoTen");
            hashtable["@HoTen"] = "%" + nguoiDung.Fullname + "%";
        }

        if (nguoiDung.NgayDK != null)
        {
            sql.Append(" AND NgayDK >= @NgayDK");
            hashtable["@NgayDK"] = nguoiDung.NgayDK;
        }

    NguoiDungNull:

        if (dateEnd != null)
        {
            sql.Append(" AND NgayDK <= @NgayDKEnd");
            hashtable["@NgayDKEnd"] = dateEnd;
        }

        sql.Append(" ORDER BY NgayDK DESC");

        SqlParameter[] para = new SqlParameter[hashtable.Count];
        int i = 0;
        foreach (DictionaryEntry entry in hashtable)
        {
            para[i] = new SqlParameter(Convert.ToString(entry.Key), entry.Value);
            i++;
        }
        return ConDB.ExecuteQuery(sql.ToString(), para);
    }

    public DataTable GetNguoiDungByEmail(string email)
    {
        string sql = "SELECT * FROM T_NGUOI_DUNG WHERE Email=@Email";
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@Email", email);
        return ConDB.ExecuteQuery(sql, para);
    }

    public DataTable GetNguoiDungByUsername(string username)
    {
        string sql = "SELECT * FROM T_NGUOI_DUNG WHERE UserName=@UserName";
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@UserName", username);
        return ConDB.ExecuteQuery(sql, para);
    }

    public bool InsertNguoiDung(NguoiDung user)
    {
        string sql = "INSERT INTO T_NGUOI_DUNG(HoTen, UserName, Email, MatKhau, DienThoai) VALUES(@HoTen, @UserName, @Email, @MatKhau, @DienThoai)";
        SqlParameter[] para = new SqlParameter[5];
        para[0] = new SqlParameter("@HoTen", user.Fullname);
        para[1] = new SqlParameter("@UserName", user.Username);
        para[2] = new SqlParameter("@Email", user.Email);
        para[3] = new SqlParameter("@MatKhau", user.MatKhau);
        para[4] = new SqlParameter("@DienThoai", user.DienThoai);
        return ConDB.ExecuteNonQuery(sql, para);
    }

    public bool DeleteNguoiDung(NguoiDung nct)
    {
        string sql = "DELETE FROM T_NGUOI_DUNG WHERE UserName=@UserName";
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@UserName", nct.Username);
        return ConDB.ExecuteNonQuery(sql, para);
    }

    public bool UpdateNguoiDungAdmin(NguoiDung nct)
    {
        string sql = "UPDATE T_NGUOI_DUNG SET HoTen=@HoTen, Lock=@Lock WHERE UserName=@UserName";
        SqlParameter[] para = new SqlParameter[3];
        para[0] = new SqlParameter("@HoTen", nct.Fullname);
        para[1] = new SqlParameter("@Lock", nct.Lock);
        para[2] = new SqlParameter("@UserName", nct.Username);
        return ConDB.ExecuteNonQuery(sql, para);
    }
}